Defining data cleansing rules
Qlik Compose enables you to define data cleansing rules for each of a table’s columns. Each rule consists of a data validation condition and a cleansing process that is performed as required (i.e. if the data is not valid).
Data Cleansing rules will be applied before any filters that are defined.
To add a rule:
- Click the Manage button at the bottom of the Data Warehouse panel.
- In the Manage Data Warehouse Tasks window, click the link in the Mapping column for the relevant table.
-
In the Edit Mappings - <Name> window, click the Data Quality toolbar button. The Data Quality Rules - <Table Name> window opens.
-
To add a new rule, click the New toolbar button. A row is added to the rules table.
- In the Name column, specify a name for the rule.
- From the drop-down list in the Column column, select the column to which the rule will be applied.
- Hover the mouse-cursor over the Condition column and then click the fx button that appears on the right.
-
In the Edit Condition Rule window, create a condition (using an expression) that the data in the column must meet in order to be considered valid. For more information on creating expressions, see Opening the expression builder.
See also Simple Example Rule below.
- From the drop-down list in the If Condition is False column, select Cleanse Silently.
- Hover the mouse-cursor over the Correction column and then click the fx button that appears on the right.
-
In the Edit Correction Rule window, create an expression to cleanse the data. For more information on creating expressions, see Opening the expression builder.
See also Simple Example Rule below.
- In the Description column, enter a description for the rule.
- In the Enabled column, select or clear the check box to enable (the default) or disable the rule respectively.
Simple Example Rule
The condition expression on the left stipulates that the product ID number must be less than 100. If it is greater than or equal to 100, the data will be corrected using the expression on the right.
${ProductID} < 100 ${ProductID} - 100